from django.db import models
from django.contrib.auth import get_user_model
from django.utils import timezone

User = get_user_model()

# Create your models here.

class VesselSchedule(models.Model):
    """
    船舶航线数据模型
    基于README.md中的字段设计
    实现了数据版本控制和历史记录
    """
    # 自增主键
    id = models.AutoField(primary_key=True, verbose_name="ID")
    
    # 核心字段（组合主键的组成部分）
    polCd = models.CharField(max_length=10, verbose_name="起运港五字码")
    podCd = models.CharField(max_length=10, verbose_name="目的港五字码")
    vessel = models.CharField(max_length=100, verbose_name="船名")
    voyage = models.CharField(max_length=50, verbose_name="航次")
    data_version = models.IntegerField(verbose_name="数据版本号")
    
    # 时间与版本控制字段
    fetch_timestamp = models.BigIntegerField(verbose_name="数据抓取的Unix时间戳")
    fetch_date = models.DateTimeField(verbose_name="格式化的日期时间")
    status = models.SmallIntegerField(default=1, verbose_name="数据状态：1-有效，0-无效")
    
    # 航线基本信息
    routeCd = models.CharField(max_length=50, blank=True, null=True, verbose_name="航线服务名称")
    routeEtd = models.CharField(max_length=20, blank=True, null=True, verbose_name="计划离港班期")
    carriercd = models.CharField(max_length=20, blank=True, null=True, verbose_name="船公司英文名")
    isReferenceCarrier = models.CharField(max_length=5, blank=True, null=True, verbose_name="是否主船东")
    imo = models.CharField(max_length=20, blank=True, null=True, verbose_name="国际海事组织号码")
    shipAgency = models.CharField(max_length=100, blank=True, null=True, verbose_name="船代")
    
    # 港口信息
    pol = models.CharField(max_length=100, blank=True, null=True, verbose_name="起运港英文名")
    polTerminal = models.CharField(max_length=100, blank=True, null=True, verbose_name="起运港码头")
    polTerminalCd = models.CharField(max_length=20, blank=True, null=True, verbose_name="起运港码头代码")
    pod = models.CharField(max_length=100, blank=True, null=True, verbose_name="目的港英文名")
    podTerminal = models.CharField(max_length=100, blank=True, null=True, verbose_name="目的港码头")
    podTerminalCd = models.CharField(max_length=20, blank=True, null=True, verbose_name="目的港码头代码")
    
    # 航运时间相关
    eta = models.CharField(max_length=30, blank=True, null=True, verbose_name="计划到港日期")
    etd = models.CharField(max_length=30, blank=True, null=True, verbose_name="计划离港日期")
    totalDuration = models.CharField(max_length=10, blank=True, null=True, verbose_name="预计航程")
    bookingCutoff = models.CharField(max_length=30, blank=True, null=True, verbose_name="截订舱时间")
    cyOpen = models.CharField(max_length=30, blank=True, null=True, verbose_name="进港时间")
    cyClose = models.CharField(max_length=30, blank=True, null=True, verbose_name="截港时间")
    customCutoff = models.CharField(max_length=30, blank=True, null=True, verbose_name="截放行")
    cutOff = models.CharField(max_length=30, blank=True, null=True, verbose_name="截申报")
    siCutoff = models.CharField(max_length=30, blank=True, null=True, verbose_name="截单时间")
    vgmCutoff = models.CharField(max_length=30, blank=True, null=True, verbose_name="截VGM时间")
    
    # 中转相关信息
    isTransit = models.CharField(max_length=5, blank=True, null=True, verbose_name="是否中转")
    transitPortEn = models.CharField(max_length=100, blank=True, null=True, verbose_name="中转1港口名")
    transitPortCd = models.CharField(max_length=10, blank=True, null=True, verbose_name="中转1港口代码")
    vesselAfterTransit = models.CharField(max_length=100, blank=True, null=True, verbose_name="中转1船名")
    voyageAfterTransit = models.CharField(max_length=50, blank=True, null=True, verbose_name="中转1航次")
    secondTransitPortEn = models.CharField(max_length=100, blank=True, null=True, verbose_name="中转2港口名")
    secondTransitPortCd = models.CharField(max_length=10, blank=True, null=True, verbose_name="中转2港口代码")
    secondVesselAfterTransit = models.CharField(max_length=100, blank=True, null=True, verbose_name="中转2船名")
    secondVoyageAfterTransit = models.CharField(max_length=50, blank=True, null=True, verbose_name="中转2航次")
    
    # 其他与扩展字段
    shareCabins = models.TextField(blank=True, null=True, verbose_name="共舱结果集")
    remark = models.TextField(blank=True, null=True, verbose_name="备注信息")
    ext_field1 = models.CharField(max_length=255, blank=True, null=True, verbose_name="扩展字段1")
    ext_field2 = models.CharField(max_length=255, blank=True, null=True, verbose_name="扩展字段2")
    ext_field3 = models.TextField(blank=True, null=True, verbose_name="扩展字段3")
    
    class Meta:
        """元数据类"""
        db_table = 'vessel_schedule'  # 数据库表名
        verbose_name = '船舶航线'
        verbose_name_plural = '船舶航线'
        # 组合唯一约束，确保在同一版本中不会有重复的航线
        unique_together = ('polCd', 'podCd', 'vessel', 'voyage', 'data_version')
        
    def __str__(self):
        """字符串表示"""
        return f"{self.vessel} {self.voyage}: {self.pol}({self.polCd}) → {self.pod}({self.podCd})"


class VesselInfoFromCompany(models.Model):
    """
    船舶价格和舱位,截关时间补充信息
    与VesselSchedule表关联，通过polCd, podCd, vessel, voyage,carrierCd五个字段关联
    """
    # 关联字段
    carriercd = models.CharField(max_length=10, verbose_name="船公司")  # 对应VesselSchedule的carriercd
    polCd = models.CharField(max_length=10, verbose_name="起运港五字码")
    podCd = models.CharField(max_length=10, verbose_name="目的港五字码")
    vessel = models.CharField(max_length=100, verbose_name="船名")
    voyage = models.CharField(max_length=50, verbose_name="航次")
    
    # 补充字段
    gp_20 = models.CharField(max_length=50, blank=True, null=True, verbose_name="20尺普通箱")
    hq_40 = models.CharField(max_length=50, blank=True, null=True, verbose_name="40尺高箱")
    cut_off_time = models.CharField(max_length=50, blank=True, null=True, verbose_name="截关时间")
    price = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True, verbose_name="价格")
    
    class Meta:
        """元数据类"""
        db_table = 'vessel_info_from_company'  # 数据库表名
        verbose_name = '船舶额外信息'
        verbose_name_plural = '船舶额外信息'
        # 组合唯一约束
        unique_together = ('carriercd', 'polCd', 'podCd', 'vessel', 'voyage')
        
    def __str__(self):
        """字符串表示"""
        return f"{self.vessel} {self.voyage}: {self.carriercd} {self.polCd} → {self.podCd}, ¥{self.price if self.price else '--'}"


class RouteSearchLog(models.Model):
    """
    航线搜索日志模型
    记录用户每次搜索的航线组合，用于生成热门航线
    """
    user = models.ForeignKey(
        User, 
        on_delete=models.CASCADE, 
        null=True, 
        blank=True,
        verbose_name="搜索用户",
        help_text="执行搜索的用户，匿名用户可为空"
    )
    pol_cd = models.CharField(
        max_length=10, 
        verbose_name="起运港代码",
        help_text="搜索的起运港五字码"
    )
    pod_cd = models.CharField(
        max_length=10, 
        verbose_name="目的港代码", 
        help_text="搜索的目的港五字码"
    )
    search_time = models.DateTimeField(
        default=timezone.now,
        verbose_name="搜索时间",
        help_text="用户执行搜索的时间"
    )
    ip_address = models.GenericIPAddressField(
        null=True, 
        blank=True,
        verbose_name="IP地址",
        help_text="用户的IP地址"
    )
    user_agent = models.TextField(
        blank=True,
        verbose_name="用户代理",
        help_text="浏览器用户代理字符串"
    )
    results_count = models.IntegerField(
        default=0,
        verbose_name="结果数量",
        help_text="搜索返回的结果数量"
    )
    
    class Meta:
        db_table = 'route_search_log'
        verbose_name = '航线搜索日志'
        verbose_name_plural = '航线搜索日志'
        ordering = ['-search_time']
        indexes = [
            models.Index(fields=['pol_cd', 'pod_cd']),
            models.Index(fields=['search_time']),
            models.Index(fields=['user']),
        ]
    
    def __str__(self):
        user_info = self.user.email if self.user else "匿名用户"
        return f"{user_info}: {self.pol_cd} → {self.pod_cd} ({self.search_time.strftime('%Y-%m-%d %H:%M')})"


class PopularRoute(models.Model):
    """
    热门航线模型
    基于搜索频次计算的热门航线，定期更新
    """
    pol_cd = models.CharField(
        max_length=10,
        verbose_name="起运港代码"
    )
    pod_cd = models.CharField(
        max_length=10,
        verbose_name="目的港代码"
    )
    route_name = models.CharField(
        max_length=100,
        verbose_name="航线名称",
        help_text="显示名称，如'深圳 → 新加坡'"
    )
    search_count = models.IntegerField(
        default=0,
        verbose_name="搜索次数",
        help_text="总搜索次数"
    )
    search_count_7d = models.IntegerField(
        default=0,
        verbose_name="7天搜索次数",
        help_text="最近7天的搜索次数"
    )
    search_count_30d = models.IntegerField(
        default=0,
        verbose_name="30天搜索次数",
        help_text="最近30天的搜索次数"
    )
    unique_users_count = models.IntegerField(
        default=0,
        verbose_name="唯一用户数",
        help_text="搜索过该航线的唯一用户数"
    )
    last_search_time = models.DateTimeField(
        null=True,
        blank=True,
        verbose_name="最后搜索时间"
    )
    popularity_score = models.FloatField(
        default=0.0,
        verbose_name="热度评分",
        help_text="综合热度评分，用于排序"
    )
    updated_at = models.DateTimeField(
        auto_now=True,
        verbose_name="更新时间"
    )
    
    class Meta:
        db_table = 'popular_routes'
        verbose_name = '热门航线'
        verbose_name_plural = '热门航线'
        unique_together = ('pol_cd', 'pod_cd')
        ordering = ['-popularity_score', '-search_count']
        indexes = [
            models.Index(fields=['popularity_score']),
            models.Index(fields=['search_count_7d']),
            models.Index(fields=['search_count_30d']),
        ]
    
    def __str__(self):
        return f"{self.route_name} (搜索{self.search_count}次, 热度{self.popularity_score:.1f})"
    
    def calculate_popularity_score(self):
        """
        计算热度评分
        综合考虑总搜索次数、近期搜索次数和用户多样性
        """
        # 基础分数：总搜索次数
        base_score = self.search_count
        
        # 近期活跃度加权：7天搜索次数 * 3，30天搜索次数 * 1.5
        recent_boost = self.search_count_7d * 3 + self.search_count_30d * 1.5
        
        # 用户多样性加权：唯一用户数 * 2
        diversity_boost = self.unique_users_count * 2
        
        # 时间衰减：如果最近没有搜索，降低分数
        if self.last_search_time:
            days_since_last = (timezone.now() - self.last_search_time).days
            if days_since_last > 30:
                time_decay = 0.5
            elif days_since_last > 7:
                time_decay = 0.8
            else:
                time_decay = 1.0
        else:
            time_decay = 0.1
        
        self.popularity_score = (base_score + recent_boost + diversity_boost) * time_decay
        return self.popularity_score

